package cn.edu.hbwe.book.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.edu.hbwe.book.entity.Book;
import cn.edu.hbwe.book.entity.OrderItem;
import cn.edu.hbwe.book.entity.Orders;
import cn.edu.hbwe.book.utils.C3P0Util;

public class BookDao {
	private static List<Book> blist = new ArrayList<Book>();
	private static QueryRunner run = new QueryRunner(C3P0Util.getDataSource());

	// 根据id查找商品
	public Book findBookById(String id) throws SQLException {
		String sql = "select * from products where id=?";
		Book book = run.query(sql, new BeanHandler<Book>(Book.class), id);
		return book;
	}

	// 生成订单时，将商品数量减少
	public void changeBookNum(Orders order) throws SQLException {
		String sql = "update products set pnum=pnum-? where id=?";
		List<OrderItem> items = order.getOrderItems();
		Object[][] params = new Object[items.size()][2];

		for (int i = 0; i < params.length; i++) {
			params[i][0] = items.get(i).getBuyNum();
			params[i][1] = items.get(i).getBook().getId();
		}

		run.batch(sql, params);
	}

	// 获取数据总条数
	public int findAllCount(String category) throws SQLException {
		String sql = "select count(*) from products";

		if (!"全部商品".equals(category)) {
			sql += " where category=?";

			Long count = (Long) run.query(sql, new ScalarHandler(), category);
			return count.intValue();
		} else {
			Long count = (Long) run.query(sql, new ScalarHandler());

			return count.intValue();
		}
	}

	// 获取当前页数据
	public List<Book> findByPage(int currentPage, int currentCount, String category) throws SQLException {
		// 要执行的sql语句
		String sql = null;
		// 参数
		Object[] obj = null;
		// 如果category不为null,代表是按分类查找
		if (!"全部商品".equals(category)) {
			sql = "select * from products  where category=? limit ?,?";
			obj = new Object[] { category, (currentPage - 1) * currentCount, currentCount, };
		} else {
			sql = "select * from products  limit ?,?";
			obj = new Object[] { (currentPage - 1) * currentCount, currentCount, };
		}
		return run.query(sql, new BeanListHandler<Book>(Book.class), obj);
	}

	// 用于搜索框根据书名来模糊查询相应的图书
	public List<Book> findBookByName(int currentPage, int currentCount, String searchfield) throws SQLException {
		// 根据名字模糊查询图书
		String sql = "SELECT * FROM products WHERE name LIKE '%" + searchfield + "%' LIMIT ?,?";
		return run.query(sql, new BeanListHandler<Book>(Book.class), currentPage - 1, currentCount);
	}

	// 前台搜索框，根据书名模糊查询出的图书总数量
	public int findBookByNameAllCount(String searchfield) throws SQLException {
		String sql = "SELECT COUNT(*) FROM products WHERE name LIKE '%" + searchfield + "%'";
		// 查询出满足条件的总数量，为long类型
		Long count = (Long) run.query(sql, new ScalarHandler());
		return count.intValue();
	}

	// 前台，获取本周热销图书
	public List<Object[]> getWeekHotBook() throws SQLException {
		String sql = "SELECT products.id,products.name, " + " products.imgurl,SUM(orderitem.buynum) totalsalnum "
				+ " FROM orderitem,orders,products " + " WHERE orderitem.order_id = orders.id "
				+ " AND products.id = orderitem.product_id " + " AND orders.paystate=1 "
				+ " AND orders.orderTime > DATE_SUB(NOW(), INTERVAL 7 DAY) "
				+ " GROUP BY products.id,products.name,products.imgurl " + " ORDER BY totalsalnum DESC "
				+ " LIMIT 0,2 ";
		return run.query(sql, new ArrayListHandler());
	}

	// 删除订单时，修改商品数量
	public void updateBookNum(List<OrderItem> items) throws SQLException {

		String sql = "update products set pnum=pnum+? where id=?";
		Object[][] params = new Object[items.size()][2];
		for (int i = 0; i < params.length; i++) {
			params[i][0] = items.get(i).getBuyNum();
			params[i][1] = items.get(i).getBook().getId();
		}
		run.batch(sql, params);
	}

}
